﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Text;

using System.IO;
using Ionic.Zip;
using System.Text.RegularExpressions;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;


namespace CSVToXLS
{
    public static class GZIP
    {
        public static void UnZIP(String ZipFlieName, out List<UnZipFlieInfo> UnZipFileInfoList)
        {
           UnZipFileInfoList = new List<UnZipFlieInfo>();
            //CSV文件直接返回
           if (Helper.IsSingleCSVFlie(ZipFlieName))
            {
                var Temp = File.ReadAllBytes(ZipFlieName);

                UnZipFileInfoList.Add(new UnZipFlieInfo
                {
                    TempFlieName = Path.GetFileNameWithoutExtension(Path.GetFileName(ZipFlieName)),
                    UnZipFlieName = ZipFlieName.ToUpper(),
                    Lines = SplitLine(Temp),
                });
                return;
            }
            //不指定编码中文文件名会遇到编码
           using (var zip = ZipFile.Read(ZipFlieName,new ReadOptions{Encoding = Encoding.Default}))
           {             
               foreach (var e in zip)
               {
                   var Temp = new MemoryStream();
                   e.Extract(Temp);
                   UnZipFileInfoList.Add(new UnZipFlieInfo
                                             {
                                                 FlieName = e.FileName,
                                                 UnZipFlieName = e.FileName.ToUpper(),
                                                 UnZipSource = Temp,
                                                 Lines = SplitLine(Temp.ToArray()),
                                                 TempFlieName = e.FileName.Substring(0,e.FileName.LastIndexOf('.')),
                                                 ExcelFlieName = String.Concat(e.FileName.Substring(0, e.FileName.LastIndexOf('.')), ".xls")
                                             });
               }
           }           
       }

        private static List<String> SplitLine(byte[] ms)
        {
            var OneLine = Encoding.Default.GetString(ms);
            return OneLine.Split(new string[] {"\n"}, StringSplitOptions.None).ToList();
            //部分文件转有问题,原因有的文件采用0x0a0x0a来做回车换行，估计是生成的BUG
            //OneLine.Split(new string[] {"\r\n"}, StringSplitOptions.None);
        }

        public static void ZIP(String ZipFlieName,List<UnZipFlieInfo> UnZipFileInfoList)
        {
            if (Path.GetExtension(ZipFlieName).ToUpper() == ".XLS")
            {
                File.WriteAllBytes(ZipFlieName, UnZipFileInfoList.Single().ExcelInfo.ToArray());
                return;
            }
            using (var zip = new ZipFile(Encoding.Default))
            {
                UnZipFileInfoList.ForEach(f =>
                {
                    var b = f.ExcelInfo == null ? f.UnZipSource.ToArray() : f.ExcelInfo.ToArray();
                    var e = zip.AddEntry(f.FlieName,b);
                    e.FileName = f.ExcelFlieName;
                });
                zip.Save(ZipFlieName);
            }
        }
    }

    public class UnZipFlieInfo
    {
        public String FlieName { get; set; }
        public String UnZipFlieName { get;set;}
        public MemoryStream UnZipSource { get; set; }
        public List<String> Lines { get; set; }
        public MemoryStream ExcelInfo { get; set; }
        public String ExcelFlieName { get; set; }
        public String TempFlieName;
        private readonly Regex StringNumberRule = new Regex("^(\'|\"|`)([^(\'|\"|`)]*)(\'|\"|`)?$", RegexOptions.IgnoreCase | RegexOptions.Compiled);
     
        public void Convert()
        {            
            //生成Excel文件            
            var workbook = new HSSFWorkbook();
            ExcelInfo = new MemoryStream();
            var CellStyleDouble = workbook.CreateCellStyle();
            CellStyleDouble.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
            var CellStyleInt = workbook.CreateCellStyle();
            CellStyleInt.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");

            var sheet = workbook.CreateSheet(TempFlieName);
            var LineNo = 0;

            var mySheet = new MySheet(Lines);
            mySheet.CellList.ForEach(fe =>
                                         {
                                             var Row = sheet.CreateRow(LineNo);
                                             UInt16 ColmunNo = 0;
                                             fe.ForEach(ffe =>
                                                            {
                                                                try
                                                                {
                                                                    var Cell = Row.CreateCell(ColmunNo);
                                                                    var ff = ffe.CellValue;
                                                                    if (String.IsNullOrWhiteSpace(ff))
                                                                    {
                                                                        Cell.SetCellType(CellType.STRING);
                                                                        Cell.SetCellValue(ff);
                                                                        return;
                                                                    }
                                                                    ff = ff.SupperTrim();
                                                                    var m = StringNumberRule.Match(ff);
                                                                    if (m.Success)
                                                                    {
                                                                        Cell.SetCellType(CellType.STRING);
                                                                        //去除前后单引号,双引号和撇号
                                                                        //引号中会含有\t键
                                                                        Cell.SetCellValue(m.Groups[2].Value.SupperTrim());
                                                                        return;
                                                                    }

                                                                    if (ffe.HasNumber)
                                                                    {
                                                                        //一列中既有整数又有浮点时设置数值风格和影响Sum的准确度
                                                                        if (!mySheet.ColumnHasDoubleNumber[ColmunNo])
                                                                        {
                                                                            Cell.CellStyle = CellStyleInt;
                                                                        }
                                                                        //浮点数设为0.00,整数设为0
                                                                        //Cell.CellStyle = (Math.Abs(Math.Ceiling(dd) - dd) > 0) ? CellStyleDouble : CellStyleInt;
                                                                        //不用数字类型,转到Excel有的会浮点数精度会改变如:30.5变为30.50000000002                            
                                                                        Cell.SetCellType(CellType.NUMERIC);
                                                                        Cell.SetCellValue(double.Parse(ff));
                                                                        return;
                                                                    }
                                                                    Cell.SetCellType(CellType.STRING);
                                                                    Cell.SetCellValue(ff);
                                                                }
                                                                finally
                                                                {
                                                                    ++ColmunNo;                                                                    
                                                                }     
                                                            });
                                             ++LineNo;
                                         });

            for (var i = 0; i < mySheet.MaxColumnCount; ++i)
            {
                sheet.AutoSizeColumn(i);
            }            
            workbook.Write(ExcelInfo);
        }
    }

    public class MySheet
    {
        /// <summary>
        /// 按列归类数据
        /// </summary>
        public Dictionary<UInt16,List<MyCell>> ColumnList { get; private set; }
        /// <summary>
        /// 按行列保存，第一维行，第二维列
        /// </summary>
        public List<List<MyCell>> CellList { get; set; }
        public UInt16 MaxColumnCount{ get; set; }
        public UInt16 RowCount { get; set; }
        private List<MyCell> AllCellList { get; set; }

        /// <summary>
        /// 某列中是否有浮点数
        /// </summary>
        public Dictionary<UInt16,Boolean> ColumnHasDoubleNumber { get; set; }
        private const char c = ',';

        public MySheet(List<String> Lines)
        {
            RowCount = 0;
            MaxColumnCount = 0;
            AllCellList = new List<MyCell>();
            CellList = new List<List<MyCell>>();
            Lines.ForEach(l =>
                              {
                                  var Cells = l.Split(c).ToList();
                                  UInt16 i = 0;
                                  var RowCells = new List<MyCell>();
                                  for (; i < Cells.Count; ++i)
                                  {
                                      var NumProperty = Helper.HasDouble(Cells[i]);
                                      RowCells.Add(new MyCell
                                                         {
                                                             RowNo = RowCount,
                                                             ColumnNo = i,
                                                             CellValue = Cells[i],
                                                             HasNumber = NumProperty.Item1,
                                                             HasDoubleNumber = NumProperty.Item2
                                                         });                                      
                                  }
                                  AllCellList.AddRange(RowCells);
                                  CellList.Add(RowCells);
                                  if(MaxColumnCount < i)
                                  {
                                    MaxColumnCount = i;    
                                  }                                  
                                  ++RowCount;
                              });
            var Temp = AllCellList.GroupBy(g => g.ColumnNo).Select(s => new {Key = s.Key, Values = s.ToList()}).ToDictionary(
                    d => d.Key);
            ColumnList = new Dictionary<ushort, List<MyCell>>();
            ColumnHasDoubleNumber = new Dictionary<ushort, bool>();
            for(UInt16 n = 0; n < Temp.Keys.Count; ++n)
            {
                ColumnList.Add(n,Temp[n].Values);
                //判断某列是否全是数字没有意义,因为不知道列头在第几行,列头不一定不是数字
                ColumnHasDoubleNumber.Add(n, Temp[n].Values.Any(t => t.HasDoubleNumber));
            }
            
        }
    }

    public class MyCell
    {
        public UInt16 RowNo { get; set; }
        public UInt16 ColumnNo { get; set; }
        public String CellValue { get; set; }
        public Boolean HasDoubleNumber { get; set; }
        public Boolean HasNumber { get; set; }
    }

    public static class Helper
    {
        public static String SupperTrim(this String t)
        {
            //Tab键,半角空格
            char[] charsToTrim = { '\t', ' ','\r'};
            return t.Trim(charsToTrim);
        }
        public static Boolean IsSingleCSVFlie(String FlieName)
        {
            return Path.GetExtension(FlieName).ToUpper() == ".CSV";
        }

        /// <summary>
        /// 是否数字、是否浮点数
        /// </summary>
        /// <param name="Value"></param>
        /// <returns>true - 数字，true - 浮点数</returns>
        public static Tuple<Boolean,Boolean> HasDouble(String Value)
        {
            if (String.IsNullOrWhiteSpace(Value))
            {
                return Tuple.Create(false,false);
            }
                
            double dd;
            if (double.TryParse(Value, out dd))
            {
                //Excel手册对于精度描述     是对计算的精确程度的度量。Excel 采用 15 个有效数字的精度进行存储和计算。
                //上述说明不带小数点
                //http://office.microsoft.com/zh-cn/excel-help/HP010342231.aspx
                //发现有部分浮点数导出时没有控制精点,导到CSV文件中时是30.900000000000002,但是Excel打开后做了Round处理
                //Round处理说明见的备注部分http://msdn.microsoft.com/zh-cn/library/75ks3aby.aspx
                if (Math.Round(dd,15).ToString(CultureInfo.InvariantCulture).Replace(".",String.Empty).Length > 15)
                {
                    return Tuple.Create(false, false);
                }
                //是否浮点数
                if (Math.Abs(Math.Ceiling(dd) - dd) > 0)
                {
                    return Tuple.Create(true, true);
                }
                return Tuple.Create(true, false);
            }
            return Tuple.Create(false, false);
        }
    }
}
